{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "def timetrans(a):\n",
    "    return a[0:4]+'-'+a[5:7]+'-'+a[8:10]\n",
    "\n",
    "info=pd.read_csv('sku_info.csv')\n",
    "info = pd.DataFrame(info.values, columns=['sku_id','first','second','third','brand'])\n",
    "\n",
    "sales = pd.read_csv('sku_sales.csv')\n",
    "sales.columns = ['sku_id','dc_id','date','quantity','vend','or_price','discount']\n",
    "#sales.loc[:,'date']=sales.date.map(timetrans)\n",
    "\n",
    "attr=pd.read_csv('sku_attr.csv')\n",
    "attr = pd.DataFrame(attr.values, columns=['sku_id','attr','attr_values'])\n",
    "\n",
    "prom=pd.read_csv('sku_prom.csv')\n",
    "prom = pd.DataFrame(prom.values, columns=['date','sku_id','third','prom_type'])\n",
    "prom.loc[:,'date']=prom.date.map(timetrans)\n",
    "\n",
    "test_prom=pd.read_csv('sku_prom_testing_2018Jan.csv')\n",
    "test_prom=pd.DataFrame(test_prom.values,columns=['date','sku_id','third','prom_type'])\n",
    "test_prom.loc[:,'date']=test_prom.date.map(timetrans)\n",
    "\n",
    "a=info.drop(['first','second','brand'],axis=1)\n",
    "prom09=prom[prom.sku_id!=-999]\n",
    "b=prom[prom.sku_id==-999].drop(['sku_id'],axis=1)\n",
    "prom999=pd.merge(b,a,how='left',on='third')\n",
    "prom_ok=pd.concat([prom999,prom09])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "business=pd.merge(sales,prom_ok,how='left',on=['sku_id','date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2127485, 7)\n",
      "(862250, 4)\n",
      "(3842160, 9)\n"
     ]
    }
   ],
   "source": [
    "print sales.shape\n",
    "print prom_ok.shape\n",
    "print business.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-5777340288ea>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mprint\u001b[0m \u001b[0mprom_ok\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'sku_id'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'date'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'prom_type'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36munique\u001b[1;34m(self)\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    599\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    600\u001b[0m                 \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 601\u001b[1;33m                     \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcurried\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    602\u001b[0m                 \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    603\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, *args, **kwargs)\u001b[0m\n\u001b[0;32m    714\u001b[0m         \u001b[1;31m# ignore SettingWithCopy here in case the user mutates\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    715\u001b[0m         \u001b[1;32mwith\u001b[0m \u001b[0moption_context\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'mode.chained_assignment'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 716\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_python_apply_general\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    717\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    718\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_python_apply_general\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36m_python_apply_general\u001b[1;34m(self, f)\u001b[0m\n\u001b[0;32m    718\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_python_apply_general\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    719\u001b[0m         keys, values, mutated = self.grouper.apply(f, self._selected_obj,\n\u001b[1;32m--> 720\u001b[1;33m                                                    self.axis)\n\u001b[0m\u001b[0;32m    721\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    722\u001b[0m         return self._wrap_applied_output(\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, data, axis)\u001b[0m\n\u001b[0;32m   1795\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1796\u001b[0m         \u001b[0mresult_values\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1797\u001b[1;33m         \u001b[1;32mfor\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgroup\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mzip\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgroup_keys\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msplitter\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1798\u001b[0m             \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__setattr__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgroup\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'name'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1799\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36m__iter__\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   4420\u001b[0m             \u001b[1;31m#     raise AssertionError('Start %s must be less than end %s'\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4421\u001b[0m             \u001b[1;31m#                          % (str(start), str(end)))\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4422\u001b[1;33m             \u001b[1;32myield\u001b[0m \u001b[0mi\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_chop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mslice\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mstart\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mend\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4423\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4424\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_get_sorted_data\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\groupby.pyc\u001b[0m in \u001b[0;36m_chop\u001b[1;34m(self, sdata, slice_obj)\u001b[0m\n\u001b[0;32m   4439\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4440\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_chop\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mslice_obj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4441\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0msdata\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mslice_obj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_dense\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4442\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4443\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\series.pyc\u001b[0m in \u001b[0;36m_get_values\u001b[1;34m(self, indexer)\u001b[0m\n\u001b[0;32m    707\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_get_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    708\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 709\u001b[1;33m             return self._constructor(self._data.get_slice(indexer),\n\u001b[0m\u001b[0;32m    710\u001b[0m                                      fastpath=True).__finalize__(self)\n\u001b[0;32m    711\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\internals.pyc\u001b[0m in \u001b[0;36mget_slice\u001b[1;34m(self, slobj, axis)\u001b[0m\n\u001b[0;32m   4181\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4182\u001b[0m         return self.__class__(self._block._slice(slobj),\n\u001b[1;32m-> 4183\u001b[1;33m                               self.index[slobj], fastpath=True)\n\u001b[0m\u001b[0;32m   4184\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4185\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\internals.pyc\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, block, axis, do_integrity_check, fastpath)\u001b[0m\n\u001b[0;32m   4115\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mblock\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mBlock\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4116\u001b[0m             block = make_block(block, placement=slice(0, len(axis)), ndim=1,\n\u001b[1;32m-> 4117\u001b[1;33m                                fastpath=True)\n\u001b[0m\u001b[0;32m   4118\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4119\u001b[0m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mblocks\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;33m[\u001b[0m\u001b[0mblock\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Users\\coding\\Anaconda2\\lib\\site-packages\\pandas\\core\\internals.pyc\u001b[0m in \u001b[0;36mmake_block\u001b[1;34m(values, placement, klass, ndim, dtype, fastpath)\u001b[0m\n\u001b[0;32m   2689\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mSparseArray\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2690\u001b[0m             \u001b[0mklass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mSparseBlock\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2691\u001b[1;33m         \u001b[1;32melif\u001b[0m \u001b[0missubclass\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfloating\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2692\u001b[0m             \u001b[0mklass\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mFloatBlock\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2693\u001b[0m         elif (issubclass(vtype, np.integer) and\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "print prom_ok.groupby(['sku_id','date'])['prom_type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=pd.get_dummies(prom_ok['prom_type'],prefix='prom')\n",
    "b=pd.concat([prom_ok,a],axis=1)\n",
    "c=pd.pivot_table(b,index=['date','sku_id','third'],aggfunc=np.sum).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              date  sku_id  third  prom_1  prom_10  prom_4  prom_6\n",
      "5609    2016-01-13     637     17       1        1       1       1\n",
      "6067    2016-01-14     637     17       1        1       1       1\n",
      "6529    2016-01-15     637     17       1        1       1       1\n",
      "6991    2016-01-16     637     17       1        1       1       1\n",
      "7453    2016-01-17     637     17       1        1       1       1\n",
      "7905    2016-01-18     637     17       1        1       1       1\n",
      "8360    2016-01-19     637     17       1        1       1       1\n",
      "8821    2016-01-20     637     17       1        1       1       1\n",
      "9279    2016-01-21     637     17       1        1       1       1\n",
      "9747    2016-01-22     637     17       1        1       1       1\n",
      "10218   2016-01-23     637     17       1        1       1       1\n",
      "10688   2016-01-24     637     17       1        1       1       1\n",
      "11160   2016-01-25     637     17       1        1       1       1\n",
      "21905   2016-02-17     637     17       1        1       1       1\n",
      "22378   2016-02-18     637     17       1        1       1       1\n",
      "22852   2016-02-19     637     17       1        1       1       1\n",
      "23328   2016-02-20     637     17       1        1       1       1\n",
      "23803   2016-02-21     637     17       1        1       1       1\n",
      "25230   2016-02-24     637     17       1        1       1       1\n",
      "25707   2016-02-25     637     17       1        1       1       1\n",
      "26175   2016-02-26     637     17       1        1       1       1\n",
      "26639   2016-02-27     637     17       1        1       1       1\n",
      "27103   2016-02-28     637     17       1        1       1       1\n",
      "28049   2016-03-01     637     17       1        1       1       1\n",
      "28529   2016-03-02     637     17       1        1       1       1\n",
      "29011   2016-03-03     637     17       1        1       1       1\n",
      "29490   2016-03-04     637     17       1        1       1       1\n",
      "29971   2016-03-05     637     17       1        1       1       1\n",
      "30449   2016-03-06     637     17       1        1       1       1\n",
      "30928   2016-03-07     637     17       1        1       1       1\n",
      "...            ...     ...    ...     ...      ...     ...     ...\n",
      "441546  2017-12-01     637     17       1        1       1       1\n",
      "442449  2017-12-02     637     17       1        1       1       1\n",
      "443354  2017-12-03     637     17       1        1       1       1\n",
      "444266  2017-12-04     637     17       1        1       1       1\n",
      "445186  2017-12-05     637     17       1        1       1       1\n",
      "446114  2017-12-06     637     17       1        1       1       1\n",
      "447047  2017-12-07     637     17       1        1       1       1\n",
      "447992  2017-12-08     637     17       1        1       1       1\n",
      "448934  2017-12-09     637     17       1        1       1       1\n",
      "449873  2017-12-10     637     17       1        1       1       1\n",
      "450816  2017-12-11     637     17       1        1       1       1\n",
      "451767  2017-12-12     637     17       1        1       1       1\n",
      "452713  2017-12-13     637     17       1        1       1       1\n",
      "453654  2017-12-14     637     17       1        1       1       1\n",
      "454596  2017-12-15     637     17       1        1       1       1\n",
      "455547  2017-12-16     637     17       1        1       1       1\n",
      "456493  2017-12-17     637     17       1        1       1       1\n",
      "457441  2017-12-18     637     17       1        1       1       1\n",
      "458399  2017-12-19     637     17       1        1       1       1\n",
      "459365  2017-12-20     637     17       1        1       1       1\n",
      "460326  2017-12-21     637     17       1        1       1       1\n",
      "461288  2017-12-22     637     17       1        1       1       1\n",
      "462250  2017-12-23     637     17       1        1       1       1\n",
      "463211  2017-12-24     637     17       1        1       1       1\n",
      "464171  2017-12-25     637     17       1        1       1       1\n",
      "465121  2017-12-26     637     17       1        1       1       1\n",
      "466070  2017-12-27     637     17       1        1       1       1\n",
      "467025  2017-12-28     637     17       1        1       1       1\n",
      "467977  2017-12-29     637     17       1        1       1       1\n",
      "468931  2017-12-30     637     17       1        1       1       1\n",
      "\n",
      "[427 rows x 7 columns]\n"
     ]
    }
   ],
   "source": [
    "print c[(c.prom_1!=0)&(c.prom_4!=0)&(c.prom_6!=0)&(c.prom_10!=0)&(c.sku_id==637)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sales_prom=pd.merge(sales,c,on=['sku_id','date'],how='left').drop(['third'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "table3=pd.merge(sales_prom,info,on=['sku_id'],how='left')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         sku_id  dc_id        date  quantity  vend  or_price   discount  \\\n",
      "1272        683      1  2016-05-28      20.0   1.0  0.021003   6.389881   \n",
      "2353        560      1  2016-05-26       0.0   0.0       NaN        NaN   \n",
      "7836        617      0  2016-05-30      26.0   1.0  0.031129   7.971888   \n",
      "26152       467      3  2016-05-25       0.0   1.0       NaN        NaN   \n",
      "30032       707      1  2016-05-14       0.0   1.0       NaN        NaN   \n",
      "31683       665      1  2016-05-30       0.0   1.0       NaN        NaN   \n",
      "33266       560      0  2016-05-23       0.0   0.0       NaN        NaN   \n",
      "33907       778      2  2016-05-27       0.0   0.0       NaN        NaN   \n",
      "44175       937      0  2016-05-17       0.0   0.0       NaN        NaN   \n",
      "48385       674      2  2016-05-13       0.0   1.0       NaN        NaN   \n",
      "50476       467      3  2016-05-23       0.0   1.0       NaN        NaN   \n",
      "53434       683      0  2016-05-25      50.0   1.0  0.021003   6.442857   \n",
      "55492       560      1  2016-05-29       0.0   0.0       NaN        NaN   \n",
      "63162       416      0  2016-05-13       0.0   1.0       NaN        NaN   \n",
      "77285       683      1  2016-05-30      61.0   1.0  0.021003   5.853571   \n",
      "77469       176      3  2016-05-29       0.0   1.0       NaN        NaN   \n",
      "94053       974      1  2016-05-30       5.0   1.0  0.003350   9.850746   \n",
      "106871      234      3  2016-05-24       1.0   1.0  0.022253   8.876404   \n",
      "107676      683      3  2016-05-23      15.0   0.0  0.021003   6.466667   \n",
      "111254      187      3  2016-05-28       0.0   1.0       NaN        NaN   \n",
      "129379      937      3  2016-05-14       0.0   0.0       NaN        NaN   \n",
      "136092      416      3  2016-05-15       0.0   1.0       NaN        NaN   \n",
      "138923      176      1  2016-05-24       2.0   1.0  0.004988   8.370927   \n",
      "153351      560      0  2016-05-27       0.0   0.0       NaN        NaN   \n",
      "155862      455      2  2016-05-14       1.0   1.0  0.006551   6.851145   \n",
      "157757      663      1  2016-05-13       0.0   1.0       NaN        NaN   \n",
      "163927      114      0  2016-05-14       9.0   1.0  0.015864   8.345154   \n",
      "174017      467      3  2016-05-28       0.0   1.0       NaN        NaN   \n",
      "175448      234      2  2016-05-27       0.0   1.0       NaN        NaN   \n",
      "176733      455      0  2016-05-13       0.0   1.0       NaN        NaN   \n",
      "...         ...    ...         ...       ...   ...       ...        ...   \n",
      "1974528     187      2  2016-05-24       0.0   0.0       NaN        NaN   \n",
      "1979199     234      1  2016-05-26       2.0   1.0  0.022253   8.876404   \n",
      "2003917     665      1  2016-05-29       2.0   1.0  0.006001   8.958333   \n",
      "2004355     371      1  2016-05-30      13.0   1.0  0.004601   9.203804   \n",
      "2008070     187      1  2016-05-27       0.0   0.0       NaN        NaN   \n",
      "2008071     187      1  2016-05-30       0.0   0.0       NaN        NaN   \n",
      "2014622     974      1  2016-05-29       5.0   1.0  0.003350  10.000000   \n",
      "2018945     455      0  2016-05-14       2.0   1.0  0.006551   6.851145   \n",
      "2031736     937      0  2016-05-15       0.0   0.0       NaN        NaN   \n",
      "2035221     744      1  2016-05-29       3.0   1.0  0.024316   6.375321   \n",
      "2050121     560      3  2016-05-30       0.0   1.0       NaN        NaN   \n",
      "2050726     778      2  2016-05-30       0.0   0.0       NaN        NaN   \n",
      "2056114     665      0  2016-05-25      12.0   1.0  0.006001   8.958333   \n",
      "2058460     874      0  2016-05-15      26.0   1.0  0.008407   8.902602   \n",
      "2061054     683      0  2016-05-27      67.0   1.0  0.021003   6.176786   \n",
      "2061458     744      3  2016-05-24       0.0   1.0       NaN        NaN   \n",
      "2063068     187      2  2016-05-29       3.0   0.0  0.024878   7.437186   \n",
      "2066520     560      3  2016-05-29       0.0   1.0       NaN        NaN   \n",
      "2067136     778      1  2016-05-28       0.0   0.0       NaN        NaN   \n",
      "2068982     467      1  2016-05-24       0.0   1.0       NaN        NaN   \n",
      "2079837     176      1  2016-05-25       1.0   1.0  0.004988   6.491228   \n",
      "2086644     371      3  2016-05-28      21.0   1.0  0.004601   9.019022   \n",
      "2090965     319      1  2016-05-26       0.0   0.0       NaN        NaN   \n",
      "2100606     467      0  2016-05-27       1.0   1.0  0.017002   6.985294   \n",
      "2111153     467      2  2016-05-25       0.0   0.0       NaN        NaN   \n",
      "2111950     319      2  2016-05-25       0.0   0.0       NaN        NaN   \n",
      "2115330     665      3  2016-05-29       0.0   1.0       NaN        NaN   \n",
      "2117471     234      1  2016-05-23       2.0   1.0  0.022253   8.876404   \n",
      "2119946     371      2  2016-05-25      23.0   1.0  0.004601   9.157609   \n",
      "2120195     176      3  2016-05-27       1.0   1.0  0.004988  10.000000   \n",
      "\n",
      "         prom_1  prom_10  prom_4  prom_6  first  second  third  brand  \n",
      "1272        1.0      2.0     0.0     1.0      6      25    290    254  \n",
      "2353        0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "7836        1.0      2.0     0.0     1.0      6      23    287    992  \n",
      "26152       1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "30032       0.0      2.0     0.0     0.0      7      30    167    329  \n",
      "31683       1.0      2.0     0.0     1.0      6      23    287    771  \n",
      "33266       0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "33907       1.0      2.0     0.0     0.0      6      28    143    737  \n",
      "44175       0.0      2.0     0.0     0.0      7      30    167    329  \n",
      "48385       1.0      2.0     0.0     0.0      7      30    167    810  \n",
      "50476       1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "53434       1.0      2.0     0.0     1.0      6      25    290    254  \n",
      "55492       0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "63162       1.0      2.0     0.0     1.0      7      30    167    329  \n",
      "77285       1.0      2.0     0.0     1.0      6      25    290    254  \n",
      "77469       0.0      2.0     0.0     0.0      6      26    293    387  \n",
      "94053       0.0      2.0     0.0     1.0      6      24    125    268  \n",
      "106871      1.0      2.0     1.0     1.0      6      25    290    150  \n",
      "107676      1.0      2.0     0.0     1.0      6      25    290    254  \n",
      "111254      1.0      2.0     0.0     0.0      6      25    290    281  \n",
      "129379      0.0      2.0     1.0     0.0      7      30    167    329  \n",
      "136092      1.0      2.0     0.0     1.0      7      30    167    329  \n",
      "138923      0.0      2.0     0.0     0.0      6      26    293    387  \n",
      "153351      0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "155862      1.0      2.0     0.0     1.0      7      30    167    942  \n",
      "157757      0.0      2.0     0.0     0.0      7      30    167    712  \n",
      "163927      1.0      2.0     0.0     0.0      7      30    167    424  \n",
      "174017      1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "175448      1.0      2.0     1.0     1.0      6      25    290    150  \n",
      "176733      1.0      2.0     0.0     1.0      7      30    167    942  \n",
      "...         ...      ...     ...     ...    ...     ...    ...    ...  \n",
      "1974528     1.0      2.0     0.0     0.0      6      25    290    281  \n",
      "1979199     1.0      2.0     1.0     1.0      6      25    290    150  \n",
      "2003917     1.0      2.0     0.0     1.0      6      23    287    771  \n",
      "2004355     1.0      2.0     0.0     1.0      6      24    125    268  \n",
      "2008070     1.0      2.0     0.0     0.0      6      25    290    281  \n",
      "2008071     1.0      2.0     0.0     0.0      6      25    290    281  \n",
      "2014622     0.0      2.0     0.0     1.0      6      24    125    268  \n",
      "2018945     1.0      2.0     0.0     1.0      7      30    167    942  \n",
      "2031736     0.0      2.0     1.0     0.0      7      30    167    329  \n",
      "2035221     1.0      2.0     0.0     1.0      6      25    290     51  \n",
      "2050121     0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "2050726     1.0      2.0     0.0     0.0      6      28    143    737  \n",
      "2056114     1.0      2.0     0.0     1.0      6      23    287    771  \n",
      "2058460     1.0      2.0     1.0     0.0      7      30    167    329  \n",
      "2061054     1.0      2.0     1.0     1.0      6      25    290    254  \n",
      "2061458     1.0      2.0     0.0     1.0      6      25    290     51  \n",
      "2063068     1.0      2.0     0.0     0.0      6      25    290    281  \n",
      "2066520     0.0      2.0     0.0     0.0      6      24    128    780  \n",
      "2067136     1.0      2.0     0.0     0.0      6      28    143    737  \n",
      "2068982     1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "2079837     0.0      2.0     0.0     0.0      6      26    293    387  \n",
      "2086644     0.0      2.0     0.0     1.0      6      24    125    268  \n",
      "2090965     0.0      2.0     0.0     0.0      6      23    287    190  \n",
      "2100606     1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "2111153     1.0      2.0     1.0     1.0      6      23    287    771  \n",
      "2111950     0.0      2.0     0.0     0.0      6      23    287    190  \n",
      "2115330     1.0      2.0     0.0     1.0      6      23    287    771  \n",
      "2117471     1.0      2.0     1.0     1.0      6      25    290    150  \n",
      "2119946     1.0      2.0     0.0     1.0      6      24    125    268  \n",
      "2120195     0.0      2.0     0.0     0.0      6      26    293    387  \n",
      "\n",
      "[490 rows x 15 columns]\n"
     ]
    }
   ],
   "source": [
    "print table3[table3.prom_10>=2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         sku_id  dc_id        date  quantity  vend  or_price   discount  \\\n",
      "0           637      0  2016-10-12       5.0   1.0  0.089886   7.858136   \n",
      "1           637      0  2017-09-24       1.0   1.0  0.089886   8.191933   \n",
      "2           637      3  2016-07-01       0.0   1.0       NaN        NaN   \n",
      "3           637      3  2016-08-03       2.0   1.0  0.089886   8.122392   \n",
      "4           637      3  2017-05-03       4.0   1.0  0.089886   8.136300   \n",
      "5           937      1  2016-02-12       0.0   0.0       NaN        NaN   \n",
      "6           937      1  2017-02-01       0.0   0.0       NaN        NaN   \n",
      "7           937      1  2017-03-25       0.0   0.0       NaN        NaN   \n",
      "8           937      1  2017-04-24       0.0   1.0       NaN        NaN   \n",
      "9           937      4  2017-12-14       0.0   1.0       NaN        NaN   \n",
      "10          937      5  2017-12-28       0.0   1.0       NaN        NaN   \n",
      "11          937      0  2016-01-14       2.0   1.0  0.014877   5.000000   \n",
      "12          937      2  2016-03-09       0.0   0.0       NaN        NaN   \n",
      "13          937      2  2016-07-03       7.0   1.0  0.016002   5.714062   \n",
      "14          937      2  2017-05-26       4.0   1.0  0.021240   4.973514   \n",
      "15          937      2  2017-09-25       2.0   1.0  0.021240   5.000000   \n",
      "16          937      2  2017-10-28       7.0   1.0  0.021240   4.412007   \n",
      "17          937      2  2017-12-12      18.0   1.0  0.020061   5.294448   \n",
      "18          937      3  2016-02-27       0.0   0.0       NaN        NaN   \n",
      "19          937      3  2016-03-03       0.0   0.0       NaN        NaN   \n",
      "20          937      3  2016-04-19       0.0   0.0       NaN        NaN   \n",
      "21          937      3  2016-05-09       0.0   0.0       NaN        NaN   \n",
      "22          937      3  2016-05-20       0.0   0.0       NaN        NaN   \n",
      "23          937      3  2016-10-20       0.0   0.0       NaN        NaN   \n",
      "24          937      3  2017-01-21       0.0   0.0       NaN        NaN   \n",
      "25          937      3  2017-01-24       0.0   0.0       NaN        NaN   \n",
      "26          937      3  2017-02-19       0.0   0.0       NaN        NaN   \n",
      "27          937      3  2017-05-20       0.0   1.0       NaN        NaN   \n",
      "28          177      0  2017-07-08       0.0   0.0       NaN        NaN   \n",
      "29          177      0  2017-12-02       9.0   1.0  0.001000   8.750000   \n",
      "...         ...    ...         ...       ...   ...       ...        ...   \n",
      "2127455     611      0  2017-08-09       0.0   0.0       NaN        NaN   \n",
      "2127456     611      1  2017-03-11       5.0   1.0  0.011239   9.666296   \n",
      "2127457     611      1  2017-10-30       5.0   1.0  0.012489   7.997998   \n",
      "2127458     611      2  2017-03-10       4.0   1.0  0.011239   9.860957   \n",
      "2127459     611      4  2017-02-14       2.0   1.0  0.009989  10.000000   \n",
      "2127460     611      4  2017-03-25       1.0   1.0  0.011239   9.443826   \n",
      "2127461     611      4  2017-09-13       1.0   1.0  0.012489   8.998999   \n",
      "2127462     611      3  2016-12-07       1.0   1.0  0.009114   9.588477   \n",
      "2127463     611      3  2016-12-24       0.0   1.0       NaN        NaN   \n",
      "2127464     873      5  2017-12-19       0.0   1.0       NaN        NaN   \n",
      "2127465     873      2  2016-01-25       1.0   1.0  0.012377  10.000000   \n",
      "2127466     873      2  2016-05-06       1.0   1.0  0.012377  10.000000   \n",
      "2127467     873      2  2016-12-11       0.0   1.0       NaN        NaN   \n",
      "2127468     873      2  2017-01-27       0.0   1.0       NaN        NaN   \n",
      "2127469     873      2  2017-04-11       0.0   1.0       NaN        NaN   \n",
      "2127470     873      4  2017-01-10       1.0   1.0  0.012377  10.000000   \n",
      "2127471     873      4  2017-05-21       0.0   1.0       NaN        NaN   \n",
      "2127472     873      4  2017-08-06       0.0   0.0       NaN        NaN   \n",
      "2127473     873      1  2016-01-02       1.0   0.0  0.012377  10.000000   \n",
      "2127474     873      1  2017-09-21       0.0   1.0       NaN        NaN   \n",
      "2127475     873      3  2016-08-24       0.0   1.0       NaN        NaN   \n",
      "2127476     873      3  2016-12-24       0.0   1.0       NaN        NaN   \n",
      "2127477     873      3  2017-01-10       0.0   1.0       NaN        NaN   \n",
      "2127478     873      3  2017-12-13       7.0   1.0  0.011126  10.000000   \n",
      "2127479     873      0  2016-04-12       2.0   1.0  0.012377  10.000000   \n",
      "2127480     873      0  2016-07-13       0.0   1.0       NaN        NaN   \n",
      "2127481     873      0  2016-10-14       0.0   0.0       NaN        NaN   \n",
      "2127482     873      0  2017-03-19       0.0   1.0       NaN        NaN   \n",
      "2127483     873      0  2017-08-31       0.0   1.0       NaN        NaN   \n",
      "2127484     873      0  2017-09-06       1.0   1.0  0.012377   7.979798   \n",
      "\n",
      "         prom_1  prom_10  prom_4  prom_6  first  second  third  brand  attr  \\\n",
      "0           1.0      1.0     1.0     1.0      1       4     17    177     4   \n",
      "1           1.0      1.0     1.0     1.0      1       4     17    177     4   \n",
      "2           1.0      1.0     0.0     1.0      1       4     17    177     4   \n",
      "3           1.0      1.0     1.0     1.0      1       4     17    177     4   \n",
      "4           1.0      1.0     1.0     1.0      1       4     17    177     4   \n",
      "5           0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "6           0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "7           0.0      0.0     1.0     0.0      7      30    167    329     4   \n",
      "8           0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "9           0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "10          1.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "11          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "12          NaN      NaN     NaN     NaN      7      30    167    329     4   \n",
      "13          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "14          1.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "15          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "16          1.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "17          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "18          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "19          NaN      NaN     NaN     NaN      7      30    167    329     4   \n",
      "20          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "21          0.0      1.0     1.0     0.0      7      30    167    329     4   \n",
      "22          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "23          NaN      NaN     NaN     NaN      7      30    167    329     4   \n",
      "24          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "25          0.0      1.0     0.0     0.0      7      30    167    329     4   \n",
      "26          NaN      NaN     NaN     NaN      7      30    167    329     4   \n",
      "27          1.0      0.0     0.0     0.0      7      30    167    329     4   \n",
      "28          0.0      1.0     0.0     0.0      7      30    170    901     8   \n",
      "29          1.0      1.0     1.0     0.0      7      30    170    901     8   \n",
      "...         ...      ...     ...     ...    ...     ...    ...    ...   ...   \n",
      "2127455     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127456     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127457     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127458     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127459     0.0      0.0     0.0     1.0      1       4     18    588     3   \n",
      "2127460     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127461     1.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127462     1.0      0.0     0.0     1.0      1       4     18    588     3   \n",
      "2127463     0.0      1.0     0.0     1.0      1       4     18    588     3   \n",
      "2127464     0.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "2127465     0.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127466     0.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127467     NaN      NaN     NaN     NaN      7     101    437    145     6   \n",
      "2127468     NaN      NaN     NaN     NaN      7     101    437    145     6   \n",
      "2127469     0.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "2127470     1.0      0.0     0.0     0.0      7     101    437    145     6   \n",
      "2127471     1.0      0.0     0.0     0.0      7     101    437    145     6   \n",
      "2127472     0.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "2127473     0.0      1.0     0.0     1.0      7     101    437    145     6   \n",
      "2127474     NaN      NaN     NaN     NaN      7     101    437    145     6   \n",
      "2127475     0.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127476     1.0      0.0     0.0     0.0      7     101    437    145     6   \n",
      "2127477     1.0      0.0     0.0     0.0      7     101    437    145     6   \n",
      "2127478     0.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "2127479     0.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127480     0.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127481     1.0      0.0     0.0     1.0      7     101    437    145     6   \n",
      "2127482     1.0      0.0     0.0     0.0      7     101    437    145     6   \n",
      "2127483     1.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "2127484     0.0      1.0     0.0     0.0      7     101    437    145     6   \n",
      "\n",
      "         attr_values  values_rate  \n",
      "0                  4     1.000000  \n",
      "1                  4     1.000000  \n",
      "2                  4     1.000000  \n",
      "3                  4     1.000000  \n",
      "4                  4     1.000000  \n",
      "5                  4     1.000000  \n",
      "6                  4     1.000000  \n",
      "7                  4     1.000000  \n",
      "8                  4     1.000000  \n",
      "9                  4     1.000000  \n",
      "10                 4     1.000000  \n",
      "11                 4     1.000000  \n",
      "12                 4     1.000000  \n",
      "13                 4     1.000000  \n",
      "14                 4     1.000000  \n",
      "15                 4     1.000000  \n",
      "16                 4     1.000000  \n",
      "17                 4     1.000000  \n",
      "18                 4     1.000000  \n",
      "19                 4     1.000000  \n",
      "20                 4     1.000000  \n",
      "21                 4     1.000000  \n",
      "22                 4     1.000000  \n",
      "23                 4     1.000000  \n",
      "24                 4     1.000000  \n",
      "25                 4     1.000000  \n",
      "26                 4     1.000000  \n",
      "27                 4     1.000000  \n",
      "28                 8     1.000000  \n",
      "29                 8     1.000000  \n",
      "...              ...          ...  \n",
      "2127455            5     1.666667  \n",
      "2127456            5     1.666667  \n",
      "2127457            5     1.666667  \n",
      "2127458            5     1.666667  \n",
      "2127459            5     1.666667  \n",
      "2127460            5     1.666667  \n",
      "2127461            5     1.666667  \n",
      "2127462            5     1.666667  \n",
      "2127463            5     1.666667  \n",
      "2127464            6     1.000000  \n",
      "2127465            6     1.000000  \n",
      "2127466            6     1.000000  \n",
      "2127467            6     1.000000  \n",
      "2127468            6     1.000000  \n",
      "2127469            6     1.000000  \n",
      "2127470            6     1.000000  \n",
      "2127471            6     1.000000  \n",
      "2127472            6     1.000000  \n",
      "2127473            6     1.000000  \n",
      "2127474            6     1.000000  \n",
      "2127475            6     1.000000  \n",
      "2127476            6     1.000000  \n",
      "2127477            6     1.000000  \n",
      "2127478            6     1.000000  \n",
      "2127479            6     1.000000  \n",
      "2127480            6     1.000000  \n",
      "2127481            6     1.000000  \n",
      "2127482            6     1.000000  \n",
      "2127483            6     1.000000  \n",
      "2127484            6     1.000000  \n",
      "\n",
      "[2127485 rows x 18 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "b=attr.groupby(['sku_id'])['attr'].unique().reset_index()\n",
    "b.loc[:,'attr']=b['attr'].map(lambda x: len(x))\n",
    "a=b.copy()\n",
    "\n",
    "b=attr.groupby(['sku_id'])['attr_values'].unique().reset_index()\n",
    "b.loc[:,'attr_values']=b['attr_values'].map(lambda x: len(x))\n",
    "a=pd.merge(a,b,how='left',on='sku_id')\n",
    "\n",
    "a['values_rate']=a['attr_values']/a['attr']\n",
    "\n",
    "table4=pd.merge(table3,a,how='left',on='sku_id')\n",
    "print table4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sku_id  attr  attr_values  values_rate\n",
      "0       1     2            6          3.0\n",
      "1       2     1            1          1.0\n",
      "2       3     1            1          1.0\n",
      "3       4     4            2          0.5\n",
      "4       5     4            4          1.0\n",
      "              date  dc_id  sku_id  prom_1  prom_10  prom_4  prom_6  first  \\\n",
      "185995  2018-01-31      5     996     1.0      1.0     0.0     0.0    1.0   \n",
      "185996  2018-01-31      5     997     1.0      0.0     0.0     0.0    7.0   \n",
      "185997  2018-01-31      5     998     0.0      1.0     0.0     0.0    1.0   \n",
      "185998  2018-01-31      5     999     NaN      NaN     NaN     NaN    NaN   \n",
      "185999  2018-01-31      5    1000     0.0      1.0     0.0     0.0    1.0   \n",
      "\n",
      "        second  third   brand  attr  attr_values  values_rate  \n",
      "185995     4.0   80.0   225.0     5            5     1.000000  \n",
      "185996    31.0  358.0  1153.0     7            7     1.000000  \n",
      "185997     7.0  298.0   158.0     1            1     1.000000  \n",
      "185998     NaN    NaN     NaN     7            4     0.571429  \n",
      "185999     7.0  179.0   137.0     1            1     1.000000  \n"
     ]
    }
   ],
   "source": [
    "\n",
    "###test添加attr属性\n",
    "print a.head()\n",
    "test=pd.read_csv('test.csv')\n",
    "test.drop(test.columns[[0]], axis=1,inplace=True)\n",
    "uu=pd.merge(test,a,how='left',on=['sku_id'])\n",
    "print uu.tail()\n",
    "#uu.to_csv('test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2127485, 15)\n",
      "(2127485, 18)\n",
      "(2127485, 7)\n"
     ]
    }
   ],
   "source": [
    "print table3.shape\n",
    "print table4.shape\n",
    "print sales.shape\n",
    "table4.to_csv('train.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sku_id  attr  attr_values\n",
      "1       1     1433           1\n",
      "              1523           1\n",
      "              1524           1\n",
      "              1525           1\n",
      "              1528           1\n",
      "        2     148            1\n",
      "2       60    2436           1\n",
      "3       82    1298           1\n",
      "4       68    1263           1\n",
      "        390   1263           1\n",
      "        488   433            1\n",
      "        584   1263           1\n",
      "5       22    174            1\n",
      "        37    299            1\n",
      "        103   1194           1\n",
      "        277   1553           1\n",
      "6       42    20             1\n",
      "        765   1435           1\n",
      "        876   4              1\n",
      "        877   4582           1\n",
      "        878   4583           1\n",
      "        879   4584           1\n",
      "              4585           1\n",
      "        880   4586           1\n",
      "        881   1447           1\n",
      "7       50    2065           1\n",
      "        68    2054           1\n",
      "        82    1298           1\n",
      "        225   409            1\n",
      "        390   2062           1\n",
      "                            ..\n",
      "994     390   1199           1\n",
      "        488   433            1\n",
      "        492   541            1\n",
      "        495   1263           1\n",
      "        526   1263           1\n",
      "        529   1263           1\n",
      "        530   1263           1\n",
      "        531   1263           1\n",
      "995     277   2407           1\n",
      "996     1     2617           1\n",
      "        43    339            1\n",
      "        237   967            1\n",
      "        436   203            1\n",
      "        437   138            1\n",
      "997     68    3157           1\n",
      "        225   268            1\n",
      "        397   3159           1\n",
      "        492   1263           1\n",
      "        540   433            1\n",
      "        541   3158           1\n",
      "        543   3161           1\n",
      "998     60    2435           1\n",
      "999     71    2444           1\n",
      "        109   1334           1\n",
      "        210   4              1\n",
      "        277   4607           1\n",
      "        411   4              1\n",
      "        884   4              1\n",
      "        885   4              1\n",
      "1000    277   675            1\n",
      "Name: count, Length: 6776, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "attr['count']=pd.Series(1, index=attr.index)\n",
    "bbb=attr.groupby(['sku_id','attr','attr_values'])['count'].sum()\n",
    "print bbb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             date prom_type sku_id third\n",
      "0      2018-01-01        10      1   366\n",
      "1      2018-01-02        10      1   366\n",
      "2      2018-01-03        10      1   366\n",
      "3      2018-01-04        10      1   366\n",
      "4      2018-01-05        10      1   366\n",
      "5      2018-01-06        10      1   366\n",
      "6      2018-01-07        10      1   366\n",
      "7      2018-01-08        10      1   366\n",
      "8      2018-01-09        10      1   366\n",
      "9      2018-01-10        10      1   366\n",
      "10     2018-01-11        10      1   366\n",
      "11     2018-01-12        10      1   366\n",
      "12     2018-01-13        10      1   366\n",
      "13     2018-01-14        10      1   366\n",
      "14     2018-01-15        10      1   366\n",
      "15     2018-01-16        10      1   366\n",
      "16     2018-01-17        10      1   366\n",
      "17     2018-01-18        10      1   366\n",
      "18     2018-01-19        10      1   366\n",
      "19     2018-01-20        10      1   366\n",
      "20     2018-01-21        10      1   366\n",
      "21     2018-01-22        10      1   366\n",
      "22     2018-01-23        10      1   366\n",
      "23     2018-01-11         4      1   366\n",
      "24     2018-01-12         4      1   366\n",
      "25     2018-01-13         4      1   366\n",
      "26     2018-01-14         4      1   366\n",
      "27     2018-01-15         4      1   366\n",
      "28     2018-01-16         4      1   366\n",
      "29     2018-01-17         4      1   366\n",
      "...           ...       ...    ...   ...\n",
      "42133  2018-01-14         6    999   297\n",
      "42134  2018-01-15         6    999   297\n",
      "42135  2018-01-16         6    999   297\n",
      "42136  2018-01-17         6    999   297\n",
      "42137  2018-01-18         6    999   297\n",
      "42138  2018-01-19         6    999   297\n",
      "42139  2018-01-15         1   1000   179\n",
      "42140  2018-01-16         1   1000   179\n",
      "42141  2018-01-17         1   1000   179\n",
      "42142  2018-01-18         1   1000   179\n",
      "42143  2018-01-19         1   1000   179\n",
      "42144  2018-01-20         1   1000   179\n",
      "42145  2018-01-21         1   1000   179\n",
      "42146  2018-01-22        10   1000   179\n",
      "42147  2018-01-23        10   1000   179\n",
      "42148  2018-01-24        10   1000   179\n",
      "42149  2018-01-25        10   1000   179\n",
      "42150  2018-01-26        10   1000   179\n",
      "42151  2018-01-27        10   1000   179\n",
      "42152  2018-01-28        10   1000   179\n",
      "42153  2018-01-29        10   1000   179\n",
      "42154  2018-01-30        10   1000   179\n",
      "42155  2018-01-31        10   1000   179\n",
      "42156  2018-01-08         1   1000   179\n",
      "42157  2018-01-09         1   1000   179\n",
      "42158  2018-01-10         1   1000   179\n",
      "42159  2018-01-11         1   1000   179\n",
      "42160  2018-01-12         1   1000   179\n",
      "42161  2018-01-13         1   1000   179\n",
      "42162  2018-01-14         1   1000   179\n",
      "\n",
      "[42163 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "aa=info.drop(['first','second','brand'],axis=1)\n",
    "test_prom09=test_prom[test_prom.sku_id!=-999]\n",
    "bb=test_prom[test_prom.sku_id==-999].drop(['sku_id'],axis=1)\n",
    "test_prom999=pd.merge(bb,aa,how='left',on='third')\n",
    "test_prom_ok=pd.concat([test_prom999,test_prom09])\n",
    "print test_prom_ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "a=pd.get_dummies(test_prom_ok['prom_type'],prefix='prom')\n",
    "b=pd.concat([test_prom_ok,a],axis=1)\n",
    "ccc=pd.pivot_table(b,index=['date','sku_id','third'],aggfunc=np.sum).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "test1= pd.merge(ccc.drop(['third'],axis=1),info,on=['sku_id'],how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             date  sku_id  prom_1  prom_10  prom_4  prom_6  first  second  \\\n",
      "0      2018-01-01       1       0        1       0       0      1       5   \n",
      "1      2018-01-01       2       0        1       0       0      1       7   \n",
      "2      2018-01-01       3       0        1       0       0      7      29   \n",
      "3      2018-01-01       4       1        0       0       0      7      29   \n",
      "4      2018-01-01       5       1        1       0       0      1       4   \n",
      "5      2018-01-01       6       1        0       0       0      1       3   \n",
      "6      2018-01-01       7       1        1       0       1      7      29   \n",
      "7      2018-01-01       8       0        1       0       0      7      30   \n",
      "8      2018-01-01       9       0        1       0       0      7      29   \n",
      "9      2018-01-01      10       1        1       0       0      1       4   \n",
      "10     2018-01-01      11       1        0       0       0      7      30   \n",
      "11     2018-01-01      13       1        0       0       0      7      29   \n",
      "12     2018-01-01      14       0        1       0       0      7      29   \n",
      "13     2018-01-01      15       1        1       0       0      1       5   \n",
      "14     2018-01-01      16       1        1       0       0      1       5   \n",
      "15     2018-01-01      17       0        1       0       0      7      29   \n",
      "16     2018-01-01      19       1        0       0       0      1       4   \n",
      "17     2018-01-01      20       1        0       0       0      7      30   \n",
      "18     2018-01-01      22       0        1       0       0      7      29   \n",
      "19     2018-01-01      24       1        0       0       0      7      31   \n",
      "20     2018-01-01      25       0        1       0       0      7      30   \n",
      "21     2018-01-01      26       1        0       0       0      1       3   \n",
      "22     2018-01-01      28       1        0       0       0      1       4   \n",
      "23     2018-01-01      30       1        1       0       0      7      29   \n",
      "24     2018-01-01      31       1        0       0       0      7      30   \n",
      "25     2018-01-01      32       0        1       0       0      7      30   \n",
      "26     2018-01-01      33       1        1       0       1      1       4   \n",
      "27     2018-01-01      36       1        1       0       0      1       5   \n",
      "28     2018-01-01      37       1        0       0       0      7      29   \n",
      "29     2018-01-01      39       1        1       0       0      1       5   \n",
      "...           ...     ...     ...      ...     ...     ...    ...     ...   \n",
      "27062  2018-01-31     963       1        0       0       1      1       3   \n",
      "27063  2018-01-31     964       1        0       0       0      7      29   \n",
      "27064  2018-01-31     966       1        1       0       0      7     101   \n",
      "27065  2018-01-31     967       1        0       0       0      7      40   \n",
      "27066  2018-01-31     968       1        1       0       0      1       7   \n",
      "27067  2018-01-31     969       1        1       0       0      1       7   \n",
      "27068  2018-01-31     970       1        0       0       0      1       4   \n",
      "27069  2018-01-31     971       0        1       0       0      7      29   \n",
      "27070  2018-01-31     972       1        1       0       0      1       3   \n",
      "27071  2018-01-31     973       1        0       0       1      1       4   \n",
      "27072  2018-01-31     974       0        1       0       0      6      24   \n",
      "27073  2018-01-31     975       1        1       0       1      7      29   \n",
      "27074  2018-01-31     977       1        1       0       1      7      29   \n",
      "27075  2018-01-31     978       0        1       0       1      1       2   \n",
      "27076  2018-01-31     979       1        1       0       0      1       7   \n",
      "27077  2018-01-31     981       0        1       0       1      7      29   \n",
      "27078  2018-01-31     982       1        1       0       0      1       2   \n",
      "27079  2018-01-31     983       1        1       0       1      7      29   \n",
      "27080  2018-01-31     984       1        0       0       0      7      29   \n",
      "27081  2018-01-31     985       1        1       0       0      7      29   \n",
      "27082  2018-01-31     986       1        1       0       0      7     101   \n",
      "27083  2018-01-31     989       0        1       0       0      7     101   \n",
      "27084  2018-01-31     991       1        0       0       0      7      30   \n",
      "27085  2018-01-31     992       1        1       0       0      1       3   \n",
      "27086  2018-01-31     993       1        1       0       0      7      29   \n",
      "27087  2018-01-31     994       1        0       0       0      7      29   \n",
      "27088  2018-01-31     996       1        1       0       0      1       4   \n",
      "27089  2018-01-31     997       1        0       0       0      7      31   \n",
      "27090  2018-01-31     998       0        1       0       0      1       7   \n",
      "27091  2018-01-31    1000       0        1       0       0      1       7   \n",
      "\n",
      "       third  brand  \n",
      "0        366    198  \n",
      "1        298    158  \n",
      "2        144   1040  \n",
      "3        145     70  \n",
      "4         17    707  \n",
      "5         15    724  \n",
      "6        145    957  \n",
      "7        168    411  \n",
      "8        149    111  \n",
      "9         80   1049  \n",
      "10       540   1264  \n",
      "11       149    518  \n",
      "12       149    749  \n",
      "13        21    285  \n",
      "14        21    534  \n",
      "15       144    394  \n",
      "16        14    738  \n",
      "17       539   1143  \n",
      "18       144    485  \n",
      "19       314   1194  \n",
      "20       170    783  \n",
      "21         6    332  \n",
      "22        18    648  \n",
      "23       144   1125  \n",
      "24       166     90  \n",
      "25       168    400  \n",
      "26        18    588  \n",
      "27        21    284  \n",
      "28       149    148  \n",
      "29        21    738  \n",
      "...      ...    ...  \n",
      "27062      4    786  \n",
      "27063    145    416  \n",
      "27064    438    777  \n",
      "27065    189    897  \n",
      "27066    179    158  \n",
      "27067    447    137  \n",
      "27068     13    396  \n",
      "27069    145   1617  \n",
      "27070     12     26  \n",
      "27071     13   1069  \n",
      "27072    125    268  \n",
      "27073    144    416  \n",
      "27074    149    957  \n",
      "27075     83    413  \n",
      "27076    179   1254  \n",
      "27077    147   1496  \n",
      "27078      1    577  \n",
      "27079    145    909  \n",
      "27080    147   1360  \n",
      "27081    145    957  \n",
      "27082    437    777  \n",
      "27083    437   1417  \n",
      "27084    166   1739  \n",
      "27085     12   1051  \n",
      "27086    149    228  \n",
      "27087    147    286  \n",
      "27088     80    225  \n",
      "27089    358   1153  \n",
      "27090    298    158  \n",
      "27091    179    137  \n",
      "\n",
      "[27092 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "print test1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " sku_id int64\n",
      "dc_id int64\n",
      "date object\n",
      "quantity float64\n",
      "vend float64\n",
      "or_price float64\n",
      "discount float64\n",
      "prom_1 float64\n",
      "prom_10 float64\n",
      "prom_4 float64\n",
      "prom_6 float64\n",
      "first int64\n",
      "second int64\n",
      "third int64\n",
      "brand int64\n",
      "attr int64\n",
      "attr_values int64\n",
      "values_rate float64\n"
     ]
    }
   ],
   "source": [
    "for i in table4:\n",
    "    print i,table4[i].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(186000L, 3L)\n",
      "              date  dc_id  sku_id\n",
      "0       2018-01-01      0       1\n",
      "1       2018-01-01      0       2\n",
      "2       2018-01-01      0       3\n",
      "3       2018-01-01      0       4\n",
      "4       2018-01-01      0       5\n",
      "5       2018-01-01      0       6\n",
      "6       2018-01-01      0       7\n",
      "7       2018-01-01      0       8\n",
      "8       2018-01-01      0       9\n",
      "9       2018-01-01      0      10\n",
      "10      2018-01-01      0      11\n",
      "11      2018-01-01      0      12\n",
      "12      2018-01-01      0      13\n",
      "13      2018-01-01      0      14\n",
      "14      2018-01-01      0      15\n",
      "15      2018-01-01      0      16\n",
      "16      2018-01-01      0      17\n",
      "17      2018-01-01      0      18\n",
      "18      2018-01-01      0      19\n",
      "19      2018-01-01      0      20\n",
      "20      2018-01-01      0      21\n",
      "21      2018-01-01      0      22\n",
      "22      2018-01-01      0      23\n",
      "23      2018-01-01      0      24\n",
      "24      2018-01-01      0      25\n",
      "25      2018-01-01      0      26\n",
      "26      2018-01-01      0      27\n",
      "27      2018-01-01      0      28\n",
      "28      2018-01-01      0      29\n",
      "29      2018-01-01      0      30\n",
      "...            ...    ...     ...\n",
      "185970  2018-01-31      5     971\n",
      "185971  2018-01-31      5     972\n",
      "185972  2018-01-31      5     973\n",
      "185973  2018-01-31      5     974\n",
      "185974  2018-01-31      5     975\n",
      "185975  2018-01-31      5     976\n",
      "185976  2018-01-31      5     977\n",
      "185977  2018-01-31      5     978\n",
      "185978  2018-01-31      5     979\n",
      "185979  2018-01-31      5     980\n",
      "185980  2018-01-31      5     981\n",
      "185981  2018-01-31      5     982\n",
      "185982  2018-01-31      5     983\n",
      "185983  2018-01-31      5     984\n",
      "185984  2018-01-31      5     985\n",
      "185985  2018-01-31      5     986\n",
      "185986  2018-01-31      5     987\n",
      "185987  2018-01-31      5     988\n",
      "185988  2018-01-31      5     989\n",
      "185989  2018-01-31      5     990\n",
      "185990  2018-01-31      5     991\n",
      "185991  2018-01-31      5     992\n",
      "185992  2018-01-31      5     993\n",
      "185993  2018-01-31      5     994\n",
      "185994  2018-01-31      5     995\n",
      "185995  2018-01-31      5     996\n",
      "185996  2018-01-31      5     997\n",
      "185997  2018-01-31      5     998\n",
      "185998  2018-01-31      5     999\n",
      "185999  2018-01-31      5    1000\n",
      "\n",
      "[186000 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "#a = pd.DataFrame(columns=['date', 'dc_id', 'sku_id'])\n",
    "a=np.zeros((186000, 3))\n",
    "print a.shape\n",
    "uu=0\n",
    "for i in range(31):\n",
    "    for j in range(6):\n",
    "        for k in range(1,1000+1):\n",
    "            a[uu,:]=[i,int(j),int(k)]\n",
    "            uu+=1\n",
    "a=pd.DataFrame(a,columns=['date', 'dc_id', 'sku_id'])\n",
    "qq=test1.date.unique()\n",
    "a.loc[:,'date']=a['date'].map(lambda x: qq[int(x)] )\n",
    "a.loc[:,'dc_id']=a['dc_id'].map(lambda x: int(x))\n",
    "a.loc[:,'sku_id']=a['sku_id'].map(lambda x: int(x) )\n",
    "print a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              date  dc_id  sku_id  prom_1  prom_10  prom_4  prom_6  first  \\\n",
      "0       2018-01-01      0       1     0.0      1.0     0.0     0.0    1.0   \n",
      "1       2018-01-01      0       2     0.0      1.0     0.0     0.0    1.0   \n",
      "2       2018-01-01      0       3     0.0      1.0     0.0     0.0    7.0   \n",
      "3       2018-01-01      0       4     1.0      0.0     0.0     0.0    7.0   \n",
      "4       2018-01-01      0       5     1.0      1.0     0.0     0.0    1.0   \n",
      "5       2018-01-01      0       6     1.0      0.0     0.0     0.0    1.0   \n",
      "6       2018-01-01      0       7     1.0      1.0     0.0     1.0    7.0   \n",
      "7       2018-01-01      0       8     0.0      1.0     0.0     0.0    7.0   \n",
      "8       2018-01-01      0       9     0.0      1.0     0.0     0.0    7.0   \n",
      "9       2018-01-01      0      10     1.0      1.0     0.0     0.0    1.0   \n",
      "10      2018-01-01      0      11     1.0      0.0     0.0     0.0    7.0   \n",
      "11      2018-01-01      0      12     NaN      NaN     NaN     NaN    NaN   \n",
      "12      2018-01-01      0      13     1.0      0.0     0.0     0.0    7.0   \n",
      "13      2018-01-01      0      14     0.0      1.0     0.0     0.0    7.0   \n",
      "14      2018-01-01      0      15     1.0      1.0     0.0     0.0    1.0   \n",
      "15      2018-01-01      0      16     1.0      1.0     0.0     0.0    1.0   \n",
      "16      2018-01-01      0      17     0.0      1.0     0.0     0.0    7.0   \n",
      "17      2018-01-01      0      18     NaN      NaN     NaN     NaN    NaN   \n",
      "18      2018-01-01      0      19     1.0      0.0     0.0     0.0    1.0   \n",
      "19      2018-01-01      0      20     1.0      0.0     0.0     0.0    7.0   \n",
      "20      2018-01-01      0      21     NaN      NaN     NaN     NaN    NaN   \n",
      "21      2018-01-01      0      22     0.0      1.0     0.0     0.0    7.0   \n",
      "22      2018-01-01      0      23     NaN      NaN     NaN     NaN    NaN   \n",
      "23      2018-01-01      0      24     1.0      0.0     0.0     0.0    7.0   \n",
      "24      2018-01-01      0      25     0.0      1.0     0.0     0.0    7.0   \n",
      "25      2018-01-01      0      26     1.0      0.0     0.0     0.0    1.0   \n",
      "26      2018-01-01      0      27     NaN      NaN     NaN     NaN    NaN   \n",
      "27      2018-01-01      0      28     1.0      0.0     0.0     0.0    1.0   \n",
      "28      2018-01-01      0      29     NaN      NaN     NaN     NaN    NaN   \n",
      "29      2018-01-01      0      30     1.0      1.0     0.0     0.0    7.0   \n",
      "...            ...    ...     ...     ...      ...     ...     ...    ...   \n",
      "185970  2018-01-31      5     971     0.0      1.0     0.0     0.0    7.0   \n",
      "185971  2018-01-31      5     972     1.0      1.0     0.0     0.0    1.0   \n",
      "185972  2018-01-31      5     973     1.0      0.0     0.0     1.0    1.0   \n",
      "185973  2018-01-31      5     974     0.0      1.0     0.0     0.0    6.0   \n",
      "185974  2018-01-31      5     975     1.0      1.0     0.0     1.0    7.0   \n",
      "185975  2018-01-31      5     976     NaN      NaN     NaN     NaN    NaN   \n",
      "185976  2018-01-31      5     977     1.0      1.0     0.0     1.0    7.0   \n",
      "185977  2018-01-31      5     978     0.0      1.0     0.0     1.0    1.0   \n",
      "185978  2018-01-31      5     979     1.0      1.0     0.0     0.0    1.0   \n",
      "185979  2018-01-31      5     980     NaN      NaN     NaN     NaN    NaN   \n",
      "185980  2018-01-31      5     981     0.0      1.0     0.0     1.0    7.0   \n",
      "185981  2018-01-31      5     982     1.0      1.0     0.0     0.0    1.0   \n",
      "185982  2018-01-31      5     983     1.0      1.0     0.0     1.0    7.0   \n",
      "185983  2018-01-31      5     984     1.0      0.0     0.0     0.0    7.0   \n",
      "185984  2018-01-31      5     985     1.0      1.0     0.0     0.0    7.0   \n",
      "185985  2018-01-31      5     986     1.0      1.0     0.0     0.0    7.0   \n",
      "185986  2018-01-31      5     987     NaN      NaN     NaN     NaN    NaN   \n",
      "185987  2018-01-31      5     988     NaN      NaN     NaN     NaN    NaN   \n",
      "185988  2018-01-31      5     989     0.0      1.0     0.0     0.0    7.0   \n",
      "185989  2018-01-31      5     990     NaN      NaN     NaN     NaN    NaN   \n",
      "185990  2018-01-31      5     991     1.0      0.0     0.0     0.0    7.0   \n",
      "185991  2018-01-31      5     992     1.0      1.0     0.0     0.0    1.0   \n",
      "185992  2018-01-31      5     993     1.0      1.0     0.0     0.0    7.0   \n",
      "185993  2018-01-31      5     994     1.0      0.0     0.0     0.0    7.0   \n",
      "185994  2018-01-31      5     995     NaN      NaN     NaN     NaN    NaN   \n",
      "185995  2018-01-31      5     996     1.0      1.0     0.0     0.0    1.0   \n",
      "185996  2018-01-31      5     997     1.0      0.0     0.0     0.0    7.0   \n",
      "185997  2018-01-31      5     998     0.0      1.0     0.0     0.0    1.0   \n",
      "185998  2018-01-31      5     999     NaN      NaN     NaN     NaN    NaN   \n",
      "185999  2018-01-31      5    1000     0.0      1.0     0.0     0.0    1.0   \n",
      "\n",
      "        second  third   brand  \n",
      "0          5.0  366.0   198.0  \n",
      "1          7.0  298.0   158.0  \n",
      "2         29.0  144.0  1040.0  \n",
      "3         29.0  145.0    70.0  \n",
      "4          4.0   17.0   707.0  \n",
      "5          3.0   15.0   724.0  \n",
      "6         29.0  145.0   957.0  \n",
      "7         30.0  168.0   411.0  \n",
      "8         29.0  149.0   111.0  \n",
      "9          4.0   80.0  1049.0  \n",
      "10        30.0  540.0  1264.0  \n",
      "11         NaN    NaN     NaN  \n",
      "12        29.0  149.0   518.0  \n",
      "13        29.0  149.0   749.0  \n",
      "14         5.0   21.0   285.0  \n",
      "15         5.0   21.0   534.0  \n",
      "16        29.0  144.0   394.0  \n",
      "17         NaN    NaN     NaN  \n",
      "18         4.0   14.0   738.0  \n",
      "19        30.0  539.0  1143.0  \n",
      "20         NaN    NaN     NaN  \n",
      "21        29.0  144.0   485.0  \n",
      "22         NaN    NaN     NaN  \n",
      "23        31.0  314.0  1194.0  \n",
      "24        30.0  170.0   783.0  \n",
      "25         3.0    6.0   332.0  \n",
      "26         NaN    NaN     NaN  \n",
      "27         4.0   18.0   648.0  \n",
      "28         NaN    NaN     NaN  \n",
      "29        29.0  144.0  1125.0  \n",
      "...        ...    ...     ...  \n",
      "185970    29.0  145.0  1617.0  \n",
      "185971     3.0   12.0    26.0  \n",
      "185972     4.0   13.0  1069.0  \n",
      "185973    24.0  125.0   268.0  \n",
      "185974    29.0  144.0   416.0  \n",
      "185975     NaN    NaN     NaN  \n",
      "185976    29.0  149.0   957.0  \n",
      "185977     2.0   83.0   413.0  \n",
      "185978     7.0  179.0  1254.0  \n",
      "185979     NaN    NaN     NaN  \n",
      "185980    29.0  147.0  1496.0  \n",
      "185981     2.0    1.0   577.0  \n",
      "185982    29.0  145.0   909.0  \n",
      "185983    29.0  147.0  1360.0  \n",
      "185984    29.0  145.0   957.0  \n",
      "185985   101.0  437.0   777.0  \n",
      "185986     NaN    NaN     NaN  \n",
      "185987     NaN    NaN     NaN  \n",
      "185988   101.0  437.0  1417.0  \n",
      "185989     NaN    NaN     NaN  \n",
      "185990    30.0  166.0  1739.0  \n",
      "185991     3.0   12.0  1051.0  \n",
      "185992    29.0  149.0   228.0  \n",
      "185993    29.0  147.0   286.0  \n",
      "185994     NaN    NaN     NaN  \n",
      "185995     4.0   80.0   225.0  \n",
      "185996    31.0  358.0  1153.0  \n",
      "185997     7.0  298.0   158.0  \n",
      "185998     NaN    NaN     NaN  \n",
      "185999     7.0  179.0   137.0  \n",
      "\n",
      "[186000 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "test2=pd.merge(a,test1,how='left',on=['date','sku_id'])\n",
    "print test2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test2.to_csv('test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
